package com.yjd.bi.etl.sql;

import com.yjd.bi.comm.utils.DbNameUtils;
import com.yjd.bi.etl.sql.enums.OrderChannel;
import com.yjd.bi.etl.sql.enums.RechChannel;
import com.yjd.comm.enums.UserEnums;

public class UserSqlConstants {
	/**
	 * 用户数统计
	 */
	// 查询充值用户数sql
	public final static String RECHARGECOUNTSQL = "SELECT count(DISTINCT(f.user_id)) FROM "+DbNameUtils.get("yjd_order")+" f, "+DbNameUtils.get("yjd_user")+" u WHERE f.user_id = u.user_id AND u.user_type & 4 != 4 AND f.status = 1 AND f.addtime BETWEEN ? AND ?";
	// 查询投资用户数sql
	public final static String INVESTCOUNTSQL = "SELECT count(DISTINCT(a.user_id)) FROM ( SELECT ct.buy_user_id as user_id FROM "+DbNameUtils.get("yjd_creditor_transfer")+" ct WHERE oktime BETWEEN ? AND ? AND  `status` = 3 UNION ALL SELECT i.user_id FROM "+DbNameUtils.get("yjd_invest")+" i WHERE addtime BETWEEN ? AND ? UNION ALL SELECT w.user_id FROM "+DbNameUtils.get("yjd_wal_project_invest")+" w WHERE create_time BETWEEN ? AND ? ) a";
	// 查询统计最大日期
	public final static String GET_MAX_USERSTATISTICS_REALTIME_DATE = "select max(statistical_date) as maxDate from " + DbNameUtils.get("yjd_user_statistics_real_time");
	// 新增统计表sql
	public final static String USERSTATISTIC_REAL_TIME_INSERTSQL = "insert into "+DbNameUtils.get("yjd_user_statistics_real_time")+"(statistical_date,recharge_account,invest_account,create_time) values (?,?,?,unix_timestamp())";

	// 查询注册用户数sql
	public final static String REGISTERCOUNTSQL = "select count(*) from "+DbNameUtils.get("yjd_user")+" where regdate BETWEEN ?  and ?";
	// 查询登陆用户数sql
	public final static String LOGINCOUNTSQL = "select count(DISTINCT(user_id)) from "+DbNameUtils.get("yjd_user_login")+" where lastlogin BETWEEN ?  and ? ";
	// 查询统计最大日期
	public final static String GET_MAX_USERSTATISTIC_DATE = "select MAX(statistical_date) from "+DbNameUtils.get("yjd_user_statistics")+"  us";
	// 数据插入统计表
	public final static String USERSTATISTIC_INSERTSQL = "insert into "+DbNameUtils.get("yjd_user_statistics")+"(statistical_date,register_account,login_account,recharge_account,invest_account,first_recharge,first_cash,create_time,first_invest,lck_activate,lck_un_activate) VALUES (?,?,?,?,?,?,?,unix_timestamp(),?,?,?)";
	// 首次充值用户数sql
	public final static String FIRST_RECHARGE_SQL = "select count(DISTINCT t.user_id) from (select min(addtime) as uptime,o.user_id from "+DbNameUtils.get("yjd_order")+" o,"+DbNameUtils.get("yjd_user")+" u where o.user_id = u.user_id and u.user_type &4!=4 and status=1 and addtime  <=?   group by user_id ) t where  uptime>= ?";
	// 首次提现用户数sql
	public final static String FIRST_CASH_SQL = "select count(DISTINCT t.user_id) from (select min(addtime) as uptime,user_id from "+DbNameUtils.get("yjd_cash")+" where status=4 and addtime  <=?   group by user_id ) t where  uptime>= ?";
	// 首次投资用户数sql
	public final static String FIRST_INVEST_SQL = "SELECT count(DISTINCT t.user_id) FROM ( SELECT user_id, min(s.uptime) AS uptime FROM ( SELECT i.user_id AS user_id, min(addtime) AS uptime FROM "+DbNameUtils.get("yjd_invest")+" i GROUP BY user_id UNION ALL SELECT ct.buy_user_id AS user_id, min(oktime) AS uptime FROM "+DbNameUtils.get("yjd_creditor_transfer")+" ct WHERE `status` = 3 GROUP BY buy_user_id ) s GROUP BY user_id ) t WHERE  t.uptime BETWEEN ? AND ?";
	// 提现用户数sql
	public final static String CASH_SQL = "SELECT count(DISTINCT a.user_id) FROM "+DbNameUtils.get("yjd_cash")+" a, "+DbNameUtils.get("yjd_user")+" u WHERE a.user_id = u.user_id and u.user_type &4!=4 and STATUS = 4 AND addtime BETWEEN ? AND ?";
	// 理财卡未激活用户数
	public final static String  LCK_UN_ACITVE ="select count(*) from yjd_user_card where  add_time <= ? and act_time = 0 and user_id = 0";
	// 理财卡激活用户数
	public final static String LCK_ACTIVE ="select count(*) from yjd_user_card where act_time between ? and ? and user_id != 0";
	//用户数统计，删除最后30天数据
	public final static String DELETE_USER_STATISTIC = "delete from yjd_user_statistics where id in (select t.id from( select id from yjd_user_statistics s order by  s.statistical_date desc limit 30)t)";


	/**
	 * 用户留存率
	 */
	// 查询某天的登录用户的user_id
	public final static String QUERY_LOGIN_USER_ID_SQL = "select distinct(user_id) from "+DbNameUtils.get("yjd_user_login")+" where lastlogin between ? and ?";

	// 查询用户的注册时间
	public final static String QUERY_REG_DATE_SQL = "select regdate from "+DbNameUtils.get("yjd_user")+" where user_id = ?";

	// 新增用户留存率
	public final static String INSERT_RETENTION_RATE = "insert "+DbNameUtils.get("yjd_login_retention_rate")+"(reg_date,login_date,user_id,create_time) values (?,?,?,unix_timestamp())";

	// 查询用户留存率表中最大的登录时间
	public final static String GET_MAX_LOGIN_DATE = "select max(login_date) as maxDate from " + DbNameUtils.get("yjd_login_retention_rate");

	/**
	 * 投资留存率
	 */
	// 查询投资留存率表中最大的登录时间
	public final static String GET_MAX_INVEST_DATE = "select max(invest_date) as maxDate from " + DbNameUtils.get("yjd_invest_retention_rate");

	// 查询某天的投资用户的user_id
	public final static String QUERY_INVEST_USER_ID_SQL = "select distinct(user_id) from "+DbNameUtils.get("yjd_invest")+" where addtime between ? and ?";

	// 新增用户投资留存率
	public final static String INSERT_INVEST_RETENTION_RATE = "insert into "+DbNameUtils.get("yjd_invest_retention_rate")+"(invest_date,user_id,create_time) values (?,?,unix_timestamp())";

	/**
	 * 根据性别查询用户数据
	 */
	// 查询充值用户数sql
	public final static String RECHARGECOUNTSQL_CONDITION = "select count(DISTINCT(user_id)) from "+DbNameUtils.get("yjd_funds_data")+" a WHERE	p_type = 1 and  (uptime  BETWEEN ? and ? )and exists(select 1 from "+DbNameUtils.get("yjd_user_idcard")+" b WHERE sex= ? and a.user_id=b.user_id)";
	// 查询投资用户数sql
	public final static String INVESTCOUNTSQL_CONDITION = "select count(distinct a.`user_id`) from (SELECT a.user_id FROM "+DbNameUtils.get("yjd_invest")+" a, "+DbNameUtils.get("yjd_user_idcard")+" b	WHERE a.user_id = b.user_id  and a.`addtime` >=? and a.`addtime` <=?  union SELECT 	a.user_id FROM "+DbNameUtils.get("yjd_creditor_transfer")+" a, "+DbNameUtils.get("yjd_user_idcard")+" b WHERE	a.user_id = b.user_id and  a.`oktime` >= ? and a.`oktime` <=? union SELECT a.user_id FROM "+DbNameUtils.get("yjd_wal_project_invest")+" a, "+DbNameUtils.get("yjd_user_idcard")+" b WHERE	a.user_id = b.user_id and  a.`create_time` >= ? and a.`create_time` <= ? ) a ,"+DbNameUtils.get("yjd_user_idcard")+" b where sex= ? and a.user_id=b.user_id";
	// 查询统计最大日期
	public final static String GET_MAX_USERSTATISTICS_REALTIME_DATE_CONDITION = "select max(statistical_date) as maxDate from " + DbNameUtils.get("yjd_user_statistics_condition");
	// 新增统计表sql
	public final static String USERSTATISTIC_INSERTSQL_CONDITION = "insert into "+DbNameUtils.get("yjd_user_statistics_condition")+"(statistical_date,register_account,login_account,recharge_account,invest_account,`condition`,create_time) values (?,?,?,?,?,?,unix_timestamp())";

	// 查询注册用户数sql
	public final static String REGISTERCOUNTSQL_CONDITION = "select count(1) from "+DbNameUtils.get("yjd_user_idcard")+" b WHERE sex= ? and b.user_id in (select user_id from yjd_user where regdate BETWEEN ?  and ?)";
	// 查询登陆用户数sql
	public final static String LOGINCOUNTSQL_CONDITION = "select count(DISTINCT(user_id)) from "+DbNameUtils.get("yjd_user_login")+" a where (lastlogin BETWEEN ? and ?  )and exists(select 1 from "+DbNameUtils.get("yjd_user_idcard")+" b WHERE sex= ? and a.user_id=b.user_id)";
	// 查询统计最大日期
	public final static String GET_MAX_USERSTATISTIC_DATE_CONDITION = "select MAX(statistical_date) from "+DbNameUtils.get("yjd_user_statistics_condition")+"  us";

	/**
	 * 平台数据-用户数据统计
	 */
	// pc用户数据统计最大统计日期
	public final static String GET_MAX_PC_USER_DATA = "select max(statistical_date) as maxDate from " + DbNameUtils.get("yjd_basic_data_pc");
	// app用户数据统计最大统计日期
	public final static String GET_MAX_APP_USER_DATA = "select max(statistical_date) as maxDate from " + DbNameUtils.get("yjd_basic_data_app");
	// wap用户数据统计最大统计日期
	public final static String GET_MAX_WAP_USER_DATA = "select max(statistical_date) as maxDate from " + DbNameUtils.get("yjd_basic_data_wap");
	// 获取注册用户数
	public final static String GET_REGISTER_USER_COUNT = "select count(*) from "+DbNameUtils.get("yjd_user")+" where  regdate BETWEEN ? and ?  and channel in (?) ";
	public final static String GET_REGISTER_USER_COUNT_MORE = "select count(*) from "+DbNameUtils.get("yjd_user")+" where  regdate BETWEEN ? and ?  and channel in (?,?) ";
	// 获取投资用户数
	public final static String GET_INVEST_USER_COUNT = "SELECT count(DISTINCT(a.user_id)) FROM ( SELECT ct.user_id FROM "+DbNameUtils.get("yjd_creditor_transfer")+" ct WHERE oktime BETWEEN ? AND ? AND ct.channel in (?) AND  `status` = 3 UNION ALL SELECT i.user_id FROM "+DbNameUtils.get("yjd_invest")+" i 	WHERE 	addtime BETWEEN ? AND ? and i.channel1 in(?) ) a";
	public final static String GET_INVEST_USER_COUNT_MORE = "SELECT count(DISTINCT(a.user_id)) FROM ( SELECT ct.user_id FROM "+DbNameUtils.get("yjd_creditor_transfer")+" ct WHERE oktime BETWEEN ? AND ? AND ct.channel in (?,?) AND  `status` = 3 UNION ALL SELECT i.user_id FROM "+DbNameUtils.get("yjd_invest")+" i 	WHERE 	addtime BETWEEN ? AND ? and i.channel1 in(?,?) ) a";
	// 获取投资金额
	public final static String GET_INVEST_SUM_PRICE = "SELECT IFNULL(sum(price),0.00) FROM ( SELECT ct.price FROM "+DbNameUtils.get("yjd_creditor_transfer")+" ct WHERE oktime BETWEEN ? AND ? AND ct.channel in (?)  AND  `status` = 3 UNION ALL SELECT i.price FROM "+DbNameUtils.get("yjd_invest")+" i 	WHERE 	addtime BETWEEN ? AND ? and i.channel1 in(?)) a ";
	public final static String GET_INVEST_SUM_PRICE_MORE = "SELECT IFNULL(sum(price),0.00) FROM ( SELECT ct.price FROM "+DbNameUtils.get("yjd_creditor_transfer")+" ct WHERE oktime BETWEEN ? AND ? AND ct.channel in (?,?)  AND  `status` = 3 UNION ALL SELECT i.price FROM "+DbNameUtils.get("yjd_invest")+" i 	WHERE 	addtime BETWEEN ? AND ? and i.channel1 in(?,?)) a ";
	//首次首次充值用户数
//	public final static String GET_FIRST_CHARGE_COUNT ="SELECT count(*) FROM ( SELECT min(addtime) AS uptime, pay_user_id as user_id, pay_client FROM yjd_app_pay p WHERE pay_STATUS = 1 GROUP BY pay_user_id ) t,yjd_user u WHERE t.user_id = u.user_id AND u.user_type & 4 != 4 AND u.channel in(?,?) AND t.pay_client = ? and t.uptime BETWEEN ? and ?";
	//首次首次投资用户数
	public final static String GET_FIRST_INVEST_COUNT= "SELECT count(*) FROM ( SELECT user_id, min(s.uptime) AS uptime, invest_channel FROM ( SELECT i.user_id AS user_id, min(addtime) AS uptime, i.channel1 as invest_channel FROM yjd_invest i GROUP BY user_id UNION ALL SELECT ct.buy_user_id AS user_id, min(oktime) AS uptime, ct.channel as invest_channel FROM yjd_creditor_transfer ct WHERE `status` = 3 GROUP BY buy_user_id ) s GROUP BY user_id ) t,yjd_user u WHERE t.user_id = u.user_id  and u.channel in (?,?) and t.invest_channel in (?) and t.uptime BETWEEN ? AND ?";

	//首次充值用户数(不区分注册端)
//	public final static String GET_FIRST_CHARGE_COUNT_ALL ="SELECT count(*) FROM ( SELECT min(addtime) AS uptime, pay_user_id as user_id, pay_client FROM yjd_app_pay p WHERE pay_STATUS = 1 GROUP BY pay_user_id ) t,yjd_user u WHERE t.user_id = u.user_id AND u.user_type & 4 != 4  AND t.pay_client = ? and t.uptime BETWEEN ? and ?";
	//首次投资用户数(不区分注册端)
	public final static String GET_FIRST_INVEST_COUNT_ALL = "SELECT count(*) FROM ( SELECT user_id, min(s.uptime) AS uptime, invest_channel FROM ( SELECT i.user_id AS user_id, min(addtime) AS uptime, i.channel1 as invest_channel FROM yjd_invest i GROUP BY user_id UNION ALL SELECT ct.buy_user_id AS user_id, min(oktime) AS uptime, ct.channel as invest_channel FROM yjd_creditor_transfer ct WHERE `status` = 3 GROUP BY buy_user_id ) s GROUP BY user_id ) t,yjd_user u WHERE t.user_id = u.user_id   and t.invest_channel in (?) and t.uptime BETWEEN ? AND ?";

	//insert
	public final static String INSERT_PC_USER_DATA = "INSERT INTO yjd_basic_data_pc ( statistical_date, register_amount, rech_amount, invest_amount, rech_price, invest_price, uptime,first_rech_amount, first_invest_amount, login_amount, borrow_price, creditor_transfer_price,first_rech_all,first_invest_all)"
			+ "VALUES (?,?,?,?,?,?,unix_timestamp(),?,?,?,?,?,?,?)";
	public final static String INSERT_APP_USER_DATA = "INSERT INTO `yjd_basic_data_app` ( `statistical_date`, `register_amount`, `rech_amount`, `invest_amount`, `rech_price`, `invest_price`, `uptime`, `first_rech_amount`, `first_invest_amount`, `login_amount`, `borrow_price`, `creditor_transfer_price`, `type`,first_rech_all,first_invest_all)  "
			+ "values (?, ?, ?, ?, ?, ?,unix_timestamp(), ?, ?, ?, ?, ?, ?,?,?)";
	public final static String INSERT_WAP_USER_DATA = "INSERT INTO `yjd_basic_data_wap` ( `statistical_date`, `register_amount`, `rech_amount`, `invest_amount`, `rech_price`, `invest_price`, `uptime`, `first_rech_amount`, `first_invest_amount`, `login_amount`, `borrow_price`, `creditor_transfer_price`, `type`,first_rech_all,first_invest_all)  "
			+ "values (?, ?, ?, ?, ?, ?,unix_timestamp(), ?, ?, ?, ?, ?, ?,?,?)";
	//pc统计，删除最后30天数据
	public final static String DELETE_PC_STATISTIC = "delete from yjd_basic_data_pc where id in (select t.id from( select id from yjd_basic_data_pc s order by  s.statistical_date desc limit 30)t)";
	//app统计，删除最后30天数据
	public final static String DELETE_APP_STATISTIC = "delete from yjd_basic_data_app where id in (select t.id from( select id from yjd_basic_data_app s order by  s.statistical_date desc limit 60)t)";
	//wap统计，删除最后30天数据
	public final static String DELETE_WAP_STATISTIC = "delete from yjd_basic_data_wap where id in (select t.id from( select id from yjd_basic_data_wap s order by  s.statistical_date desc limit 60)t)";
	//登录用户数
	public final static String GET_LOGIN_USER_COUNT = "select count(DISTINCT(user_id)) from yjd_user_login where lastlogin BETWEEN ?  and ? and channel = ? ";
	//充值用户数
//	public final static String GET_CHARGE_COUNT = "select count(DISTINCT(pay_user_id)) from yjd_app_pay a,yjd_user u where a.pay_user_id = u.user_id and pay_client = ? and pay_status = 1 AND u.user_type & 4 != 4 and addtime BETWEEN ? and ?";
	//投资用户数
	public final static String GET_INVEST_COUNT="select count(*) from ( select i.user_id from yjd_invest i where channel1 = ? and addtime between ? and ? union select buy_user_id from yjd_creditor_transfer where status = 3 and channel = ? and oktime between ? and ?) s";
	//充值金额
//	public final static String GET_CHARGE_PRICE = "select IFNULL(sum(pay_money),0.00) from yjd_app_pay a,yjd_user u where a.pay_user_id = u.user_id and pay_client = ? and pay_status = 1 AND u.user_type & 4 != 4 and addtime BETWEEN ? and ?";
	//投资金额
	public final static String GET_INVEST_PRICE = "select IFNULL(sum(price),0.00)  from ( select price from yjd_invest i where channel1 = ? and addtime between ? and ? UNION all select price from yjd_creditor_transfer where status = 3 and channel = ? and oktime between ? and ?) s";
	//投资项目金额
	public final static String GET_BORROE_PRICE ="select IFNULL(sum(price),0.00) from yjd_invest i where channel1 = ? and addtime between ? and ?";
	//债权转让金额
	public final static String GET_CREDITOR_TRANSFER_PRICE="select IFNULL(sum(price),0.00) from yjd_creditor_transfer where status = 3 and channel = ? and oktime between ? and ?";

	// 存管数据统计  ----------------- start
	// 当前已经统计了的最大日期
	public final static String MAX_STASTIC_DATA = "select max(statistical_date) as maxDate from yjd_basic_data_depo";
	// 删除最后30天数据
	public final static String DELETE_DEPO_STATISTIC = "delete from yjd_basic_data_depo where id in (select t.id from( select id from yjd_basic_data_depo s order by  s.statistical_date desc limit 30)t)";
	// 存管白名单人数
	public final static String DEPO_WHITELIST_COUNT = "select count(*) from yjd_bank_depo where addtime BETWEEN ? and ? ";
	// 开通存管人数
	public final static String DEPO_COUNT = "select count(*) from yjd_bank_depo where opentime > 0 and opentime BETWEEN ? and ? ";
	// 首次充值用户数
	public final static String FIRST_CHARGE ="SELECT COUNT(*) FROM (SELECT MIN(p.addtime) AS uptime FROM yjd_order p, yjd_bank_depo u WHERE p.status = 1 AND p.user_id = u.user_id AND p.addtime >= u.opentime AND u.opentime > 0 GROUP BY p.user_id) t WHERE t.uptime BETWEEN ? AND ?";
	// 首次投资用户数
	public final static String FIRST_INVEST= "SELECT COUNT(*) FROM (SELECT MIN(i.addtime) AS uptime FROM yjd_invest i, yjd_bank_depo u WHERE i.addtime >= u.opentime AND u.opentime > 0 AND i.user_id = u.user_id GROUP BY i.user_id) s WHERE s.uptime BETWEEN ? AND ?";
	// 开通存管后首次提现人数
	public final static String FIRST_CASHOUT = "SELECT count(*) FROM ( SELECT min(addtime) AS uptime, user_id FROM yjd_cash_dcs_order p WHERE status = 3 GROUP BY user_id ) t WHERE t.uptime BETWEEN ? and ?";
	//充值金额
	public final static String CHARGE_PRICE = "select IFNULL(sum(a.price),0.00) from yjd_order a,yjd_bank_depo u where a.user_id = u.user_id and a.status = 1 AND a.addtime >= u.opentime AND u.opentime > 0 AND a.addtime BETWEEN ? and ?";
	//投资金额
	public final static String INVEST_PRICE = "select IFNULL(sum(i.price),0.00) from yjd_invest i where i.bank_depo = 1 AND i.addtime BETWEEN ? and ?";
	//提现金额
	public final static String CASHOUT_PRICE = "select IFNULL(sum(c.price),0.00) from yjd_cash_dcs_order c where c.status = 3 AND c.addtime BETWEEN ? and ?";
	// 开通存管后绑卡人数
	public final static String DEPO_BIND_CARD= "SELECT COUNT(a.user_id) FROM yjd_bank_depo_account a, yjd_bank_depo u WHERE a.opentime >= u.opentime AND a.user_id = u.user_id AND a.opentime > 0 AND a.opentime BETWEEN ? AND ?";

	// 插入存管统计信息
	public final static String INSERT_DOPO_STASTIC_DATA = "INSERT INTO yjd_basic_data_depo (statistical_date, depo_whitelist_account, depo_account, depo_bind_card, first_recharge, first_invest, first_cashout, rech_price, invest_price, cashout_price, uptime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	// 存管数据统计  ----------------- end

	/**
	 * 首次充值用户数(区分注册端)
	 * @param orderChannel
	 * @return
	 */
	public static String get_first_charge_count(OrderChannel orderChannel) {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("SELECT count(*) FROM (SELECT min(addtime) AS uptime, user_id, p.rech_channel FROM yjd_order p WHERE p.status = 1 GROUP BY p.user_id ) t,yjd_user u WHERE t.user_id = u.user_id AND u.user_type & 4 != 4");
		switch (orderChannel) {
			case PC_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND u.channel in(%s,%s) AND (t.rech_channel in (%s,%s,%s) OR t.rech_channel is null)",
						UserEnums.Channel.NONE.getCode(),
						UserEnums.Channel.PC.getCode(),
						RechChannel.PC.getCode(),
						RechChannel.BANKPAY.getCode(),
						RechChannel.BANKPAYAUTH.getCode()));
				break;
			case IOS_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND u.channel in(%s) AND t.rech_channel in (%s,%s)",
						UserEnums.Channel.IOS.getCode(),
						RechChannel.IOS.getCode(),
						RechChannel.BANKPAYAUTH_IOS.getCode()));
				break;
			case ANDRIOD_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND u.channel in(%s) AND t.rech_channel in (%s,%s)",
						UserEnums.Channel.ANDROID.getCode(),
						RechChannel.ANDROID.getCode(),
						RechChannel.BANKPAYAUTH_ANDROID.getCode()));
				break;
			case WAP_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND u.channel in(%s) AND t.rech_channel in (%s,%s)",
						UserEnums.Channel.WAP.getCode(),
						RechChannel.WAP.getCode(),
						RechChannel.BANKPAYAUTH_WAP.getCode()));
				break;
			case WEIXIN:
				sqlBuilder.append(String.format(" AND u.channel in(%s) AND t.rech_channel in (%s,%s)",
						UserEnums.Channel.WEIXIN.getCode(),
						RechChannel.WEIXIN.getCode(),
						RechChannel.BANK_WEIXIN.getCode()));
				break;
		}
		sqlBuilder.append(" and t.uptime BETWEEN ? and ?");
		return sqlBuilder.toString();
	}

	/**
	 * 首次充值用户数(不区分注册端)
	 * @param orderChannel
	 * @return
	 */
	public static String get_first_charge_count_all(OrderChannel orderChannel){
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("SELECT count(*) FROM (SELECT min(addtime) AS uptime, user_id, p.rech_channel FROM yjd_order p WHERE p.status = 1 GROUP BY p.user_id ) t,yjd_user u WHERE t.user_id = u.user_id AND u.user_type & 4 != 4");
		switch (orderChannel) {
			case PC_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND (t.rech_channel in (%s,%s,%s) OR t.rech_channel is null)",
						RechChannel.PC.getCode(),
						RechChannel.BANKPAY.getCode(),
						RechChannel.BANKPAYAUTH.getCode()));
				break;
			case IOS_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND t.rech_channel in (%s,%s)",
						RechChannel.IOS.getCode(),
						RechChannel.BANKPAYAUTH_IOS.getCode()));
				break;
			case ANDRIOD_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND t.rech_channel in (%s,%s)",
						RechChannel.ANDROID.getCode(),
						RechChannel.BANKPAYAUTH_ANDROID.getCode()));
				break;
			case WAP_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND t.rech_channel in (%s,%s)",
						RechChannel.WAP.getCode(),
						RechChannel.BANKPAYAUTH_WAP.getCode()));
				break;
			case WEIXIN:
				sqlBuilder.append(String.format(" AND t.rech_channel in (%s,%s)",
						RechChannel.WEIXIN.getCode(),
						RechChannel.BANK_WEIXIN.getCode()));
				break;
		}
		sqlBuilder.append(" and t.uptime BETWEEN ? and ?");
		return sqlBuilder.toString();
	}
	/**
	 * 获取某个端充值用户数
	 * @param orderChannel
	 * @return
	 */
	public static String get_charge_count(OrderChannel orderChannel){
		//GET_CHARGE_COUNT = "select count(DISTINCT(pay_user_id)) from yjd_app_pay a,yjd_user u where a.pay_user_id = u.user_id and pay_client = ? and pay_status = 1 AND u.user_type & 4 != 4 and addtime BETWEEN ? and ?"
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("select count(DISTINCT(a.user_id)) from yjd_order a,yjd_user u where a.user_id = u.user_id and a.status = 1 AND u.user_type & 4 != 4");
		switch (orderChannel) {
			case PC_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND (a.rech_channel in (%s,%s,%s) OR a.rech_channel is null)",
						RechChannel.PC.getCode(),
						RechChannel.BANKPAY.getCode(),
						RechChannel.BANKPAYAUTH.getCode()));
				break;
			case IOS_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND a.rech_channel in (%s,%s)",
						RechChannel.IOS.getCode(),
						RechChannel.BANKPAYAUTH_IOS.getCode()));
				break;
			case ANDRIOD_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND a.rech_channel in (%s,%s)",
						RechChannel.ANDROID.getCode(),
						RechChannel.BANKPAYAUTH_ANDROID.getCode()));
				break;
			case WAP_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND a.rech_channel in (%s,%s)",
						RechChannel.WAP.getCode(),
						RechChannel.BANKPAYAUTH_WAP.getCode()));
				break;
			case WEIXIN:
				sqlBuilder.append(String.format(" AND a.rech_channel in (%s,%s)",
						RechChannel.WEIXIN.getCode(),
						RechChannel.BANK_WEIXIN.getCode()));
				break;
		}
		sqlBuilder.append(" and a.addtime BETWEEN ? and ?");
		return sqlBuilder.toString();
	}

	/**
	 * 获取某个端的充值金额
	 * @param orderChannel
	 * @return
	 */
	public static String get_charge_price(OrderChannel orderChannel){
		//public final static String GET_CHARGE_PRICE = "select IFNULL(sum(pay_money),0.00) from yjd_app_pay a,yjd_user u where a.pay_user_id = u.user_id and pay_client = ? and pay_status = 1 AND u.user_type & 4 != 4 and addtime BETWEEN ? and ?";
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("select IFNULL(sum(a.price),0.00) from yjd_order a,yjd_user u where a.user_id = u.user_id and a.status = 1 AND u.user_type & 4 != 4");
		switch (orderChannel) {
			case PC_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND (a.rech_channel in (%s,%s,%s) OR a.rech_channel is null)",
						RechChannel.PC.getCode(),
						RechChannel.BANKPAY.getCode(),
						RechChannel.BANKPAYAUTH.getCode()));
				break;
			case IOS_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND a.rech_channel in (%s,%s)",
						RechChannel.IOS.getCode(),
						RechChannel.BANKPAYAUTH_IOS.getCode()));
				break;
			case ANDRIOD_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND a.rech_channel in (%s,%s)",
						RechChannel.ANDROID.getCode(),
						RechChannel.BANKPAYAUTH_ANDROID.getCode()));
				break;
			case WAP_RECH_CHANNEL:
				sqlBuilder.append(String.format(" AND a.rech_channel in (%s,%s)",
						RechChannel.WAP.getCode(),
						RechChannel.BANKPAYAUTH_WAP.getCode()));
				break;
			case WEIXIN:
				sqlBuilder.append(String.format(" AND a.rech_channel in (%s,%s)",
						RechChannel.WEIXIN.getCode(),
						RechChannel.BANK_WEIXIN.getCode()));
				break;
		}
		sqlBuilder.append(" and a.addtime BETWEEN ? and ?");
		return sqlBuilder.toString();
	}

	public static void main(String[] args) {
		System.out.println(get_first_charge_count_all(OrderChannel.PC_RECH_CHANNEL));
		System.out.println(get_first_charge_count_all(OrderChannel.ANDRIOD_RECH_CHANNEL));
		System.out.println(get_first_charge_count_all(OrderChannel.IOS_RECH_CHANNEL));
		System.out.println(get_first_charge_count_all(OrderChannel.WAP_RECH_CHANNEL));
		System.out.println(get_first_charge_count_all(OrderChannel.WEIXIN));
		System.out.println(get_charge_count(OrderChannel.WAP_RECH_CHANNEL));
	}
}
